package com.weipeep.common.utils.poi;

import com.google.common.collect.Maps;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by cdliujian1 on 2015/7/30.
 */
public class Main {

    public static final String filename = "C:\\Users\\wangyun33\\Desktop\\1111111.xlsx";

    public static void main(String[] args) throws IOException, InvalidFormatException {
        //读支持.xls和.xlsx
        //testRead();
        //写入的是.xls文件，因为.xlsx兼容.xls

        testRead();
    }

    private static void testWrite() {
        ExcelHelper<UserEntity> excelHelper = new ExcelHelper<UserEntity>();
        List<UserEntity> list = new ArrayList<UserEntity>();
        list.add(new UserEntity("王雷", 22, "昌平"));
        excelHelper.writeExcel(filename, list, new ExcelWriter<UserEntity>() {
            @Override
            public void writeRow(UserEntity obj) {
                addCell("姓名", obj.getName());
                addCell("年龄", obj.getAge());
                addCell("住址", obj.getAddress());

            }
        });
    }

    private static void testRead() throws IOException, InvalidFormatException {
        ExcelHelper<Test> excelHelper = new ExcelHelper<Test>();
        ExcelResolver resolver = new ExcelResolver<Test>() {
            @Override
            public boolean resolve(Test obj) {
                String school = getCellValue("aaaa");
                String scode = getCellValue("bbbb");
                obj.setSchool(school);
                obj.setScode(scode);
                return true;
            }
        };
        List<Test> list = excelHelper.readExcel(filename, Test.class, resolver);
        Map<String, StringBuffer> map = Maps.newHashMap();
        list.forEach(test -> {
            if (map.containsKey(test.getSchool())) {
                map.get(test.getSchool()).append("'" + test.getScode() + "',");
            } else {
                map.put(test.getSchool(), new StringBuffer("'" + test.getScode() + "',"));
            }
        });
        Set<String> schools = map.keySet();
        for (String sh : schools) {
            StringBuffer sb = map.get(sh);
            String scodes = sb.toString().substring(0, sb.toString().length() - 1);
            String sql = "SELECT DISTINCT l.nSchoolId,l.sClassCode FROM bs_lesson l WHERE l.nSchoolId=" + sh + " AND l.sClassCode IN (\n" +
                    "SELECT t.sCode FROM bs_class t WHERE t.nSchoolId=" + sh + " AND t.sCode IN (" + scodes + ") AND t.sTeacher IS NULL) AND l.sTeacherCode IS NOT NULL AND l.sTeacherCode !='';";
            System.out.println(sql);

        }
    }


}
